﻿
CREATE PROCEDURE [dbo].[proc_Statistics_Performance_Trend]
	@sRq varchar(10),
	@eRq varchar(10),
	@companyId varchar(1000)
AS
BEGIN
	
	If CONVERT(varchar(7),@sRq,120)<> CONVERT(varchar(7),@eRq,120) 
	Begin
		Return 0
	End

	declare @sql varchar(max)
	set @sql='Declare @sRq Datetime,@eRq Datetime,@Yf varchar(7)
	Set @sRq = Convert(Datetime,''' + @sRq + ' 00:00:00'')
	Set @eRq = Convert(Datetime,''' + @eRq + ' 23:59:59'')
	Set @Yf = CONVERT(varchar(10),@sRq,120) 
	'
	Set @sql=@sql+'
		Select A.id,A.pid,A.name,B.CompanyId,s.XSRS,Case when s.XSRS=0 then 0 else round(B.IAA/s.XSRS,2) end XXPJML,
			B.IAA,B.IAB,B.IAA+B.IAB ML,B.IAC,B.IAF,B.fyze,B.jksr,B.jkzc,B.fyyxsr,B.jj,B.bcfk,B.sscl+isnull(p.amount,0) sscl from 
			(Select id,pid,name,CompanyId from Zzjg Where Pid in (12,13,14,281,282) and CompanyId not in(23,129) and CompanyId in('+@companyId+')) A join
			(Select CompanyId, Sum(IAA) IAA,Sum(IAB) IAB,Sum(IAC) IAC,Sum(IAF) IAF,Sum(fyze) fyze,
					Sum(jksr) jksr,Sum(jkzc) jkzc,Sum(fyyxsr) fyyxsr,Sum(jj) jj,Sum(bcfk) bcfk,Sum(sscl) sscl
			 From Statistics_Financial Where CreateDate between @sRq and @eRq
			 group by CompanyId) B On A.CompanyId=b.CompanyId left join 
			 (Select CompanyId,Sum(Amount) amount From Finance_Profit_Regulate 
					where Deleted=0 and RegulateDate between @sRq and @eRq and Approval=1
				Group by CompanyId) p On A.CompanyId=p.CompanyId 
				left join Statistics_Sales_Count s On A.CompanyId=s.CompanyId and s.YF=@Yf
		Union
		Select 999,A.pid,' +'''小计'''+',999,Sum(s.XSRS),Case when Sum(s.XSRS)=0 then 0 else round(Sum(B.IAA)/Sum(s.XSRS),2) end XXPJML,
		Sum(B.IAA),Sum(B.IAB),Sum(B.IAA+B.IAB) ML,Sum(B.IAC),Sum(B.IAF),
			Sum(B.fyze),Sum(B.jksr),Sum(B.jkzc),Sum(B.fyyxsr),Sum(B.jj),Sum(B.bcfk),Sum(B.sscl)+sum(isnull(p.amount,0)) sscl from 
			(Select id,pid,name,CompanyId from Zzjg Where Pid in (12,13,14,281,282) and CompanyId not in(23,129) and CompanyId in('+@companyId+')) A join
			(Select CompanyId, Sum(IAA) IAA,Sum(IAB) IAB,Sum(IAC) IAC,Sum(IAF) IAF,Sum(fyze) fyze,
					Sum(jksr) jksr,Sum(jkzc) jkzc,Sum(fyyxsr) fyyxsr,Sum(jj) jj,Sum(bcfk) bcfk,Sum(sscl) sscl
			 From Statistics_Financial Where CreateDate between @sRq and @eRq
			 group by CompanyId) B On A.CompanyId=b.CompanyId left join 
			 (Select CompanyId,Sum(Amount) amount From Finance_Profit_Regulate 
					where Deleted=0 and RegulateDate between @sRq and @eRq and Approval=1
				Group by CompanyId) p On A.CompanyId=p.CompanyId left join Zzjg on A.pid=Zzjg.id
				left join Statistics_Sales_Count s on A.CompanyId=s.CompanyId and s.YF=@Yf
		Group by A.pid,Zzjg.name
		Union
		Select 999,999,'+'''总计'''+',999,Sum(s.XSRS),Case when Sum(s.XSRS)=0 then 0 else round(Sum(B.IAA)/Sum(s.XSRS),2) end XXPJML,
			Sum(B.IAA),Sum(B.IAB),Sum(B.IAA+B.IAB) ML,Sum(B.IAC),Sum(B.IAF),
			Sum(B.fyze),Sum(B.jksr),Sum(B.jkzc),Sum(B.fyyxsr),Sum(B.jj),Sum(B.bcfk),Sum(B.sscl)+sum(isnull(p.amount,0)) sscl from 
			(Select id,pid,name,CompanyId from Zzjg Where Pid in (12,13,14,281,282) and CompanyId not in(23,129) and CompanyId in('+@companyId+')) A join
			(Select CompanyId, Sum(IAA) IAA,Sum(IAB) IAB,Sum(IAC) IAC,Sum(IAF) IAF,Sum(fyze) fyze,
					Sum(jksr) jksr,Sum(jkzc) jkzc,Sum(fyyxsr) fyyxsr,Sum(jj) jj,Sum(bcfk) bcfk,Sum(sscl) sscl
			 From Statistics_Financial Where CreateDate between @sRq and @eRq
			 group by CompanyId) B On A.CompanyId=b.CompanyId left join 
			 (Select CompanyId,Sum(Amount) amount From Finance_Profit_Regulate 
					where Deleted=0 and RegulateDate between @sRq and @eRq and Approval=1
				Group by CompanyId) p On A.CompanyId=p.CompanyId 
				left join Statistics_Sales_Count s On A.CompanyId=s.CompanyId and s.YF=@Yf
		Order by Pid,CompanyId'
	exec(@sql)
	print(@sql)
END
